const express = require('express');
const cors = require('cors');
const dotenv = require('dotenv');
const db = require('./config/db'); // 引入数据库配置

dotenv.config(); // 加载 .env 文件

const app = express();
const PORT = process.env.PORT || 5000;

// 中间件
// 启用 CORS
// 生产环境中建议更精确地配置允许的源
// const corsOptions = {
//   origin: process.env.FRONTEND_URL || 'http://localhost:3000' // 假设前端在3000端口
// };
// app.use(cors(corsOptions));
app.use(cors()); // 允许所有来源，方便开发

app.use(express.json()); // 解析 JSON 请求体
app.use(express.urlencoded({ extended: true })); // 解析 URL编码的请求体

// 基本路由
app.get('/', (req, res) => {
  res.json({ message: '欢迎来到 Gushen Server (Node.js)! 后端服务已启动。' });
});

// API 路由
app.get('/api/health', (req, res) => {
  res.status(200).json({ status: 'UP', message: 'Gushen Server (Node.js) 运行正常!' });
});

app.get('/api/sample_data', async (req, res) => {
  // 示例：未来可以从数据库查询数据
  // try {
  //   const [rows] = await db.pool.query('SELECT * FROM your_table_name LIMIT 10');
  //   res.status(200).json(rows);
  // } catch (error) {
  //   console.error('Error fetching sample data:', error);
  //   res.status(500).json({ error: 'Failed to retrieve data from database' });
  // }
  const sampleItems = [
    { id: 1, name: 'Node.js 示例项目A', value: 150 },
    { id: 2, name: 'Node.js 示例项目B', value: 250 },
  ];
  res.status(200).json(sampleItems);
});

// 新增接口：查询 result.lj_20250522 表的数据
app.get('/api/lj_data', async (req, res) => {
  try {
    const [rows] = await db.pool.query('SELECT * FROM result.lj_20250522');
    res.status(200).json(rows);
  } catch (error) {
    console.error('Error fetching data from result.lj_20250522:', error);
    res.status(500).json({ error: 'Failed to retrieve data from database', details: error.message });
  }
});

// --- 持仓记录 (chicang) 模块 --- 

// 定义创建 chicang 表的 SQL 语句
const createChicangTableSQL = `
CREATE TABLE IF NOT EXISTS deal_data.chicang (
  id INT AUTO_INCREMENT PRIMARY KEY,
  stock_code VARCHAR(20) NOT NULL,
  stock_name VARCHAR(50),
  buy_time DATETIME,
  buy_price DECIMAL(10, 3),
  sell_time DATETIME,
  sell_price DECIMAL(10, 3), -- 新增 sell_price 字段
  buy_quantity INT,
  profit_rate DECIMAL(10, 4),
  profit_amount DECIMAL(12, 2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`;

// API: 新增一条持仓记录
app.post('/api/chicang', async (req, res) => {
  try {
    let {
      stock_code,
      stock_name,
      buy_time,
      buy_price,
      sell_time = null, // 卖出时间可以为空
      sell_price = null, // 新增：接收 sell_price，默认为 null
      buy_quantity,
      profit_rate = null,
      profit_amount = null
    } = req.body;

    // 基本验证
    if (!stock_code || !buy_time || buy_price === undefined || buy_quantity === undefined) {
      return res.status(400).json({ error: 'Missing required fields: stock_code, buy_time, buy_price, buy_quantity' });
    }

    // 将ISO格式的日期时间字符串转换为MySQL DATETIME格式 (YYYY-MM-DD HH:MM:SS)
    const formatDateTimeForMySQL = (isoString) => {
      if (!isoString) return null;
      try {
        return new Date(isoString).toISOString().slice(0, 19).replace('T', ' ');
      } catch (e) {
        console.error('Error formatting date:', isoString, e);
        return null; // 或者抛出错误，或者返回原始值让数据库处理
      }
    };

    const formattedBuyTime = formatDateTimeForMySQL(buy_time);
    const formattedSellTime = formatDateTimeForMySQL(sell_time);

    if (buy_time && !formattedBuyTime) {
        return res.status(400).json({ error: 'Invalid buy_time format. Please use a valid ISO date string.' });
    }
    if (sell_time && !formattedSellTime) {
        return res.status(400).json({ error: 'Invalid sell_time format. Please use a valid ISO date string.' });
    }

    const [result] = await db.pool.query(
      'INSERT INTO deal_data.chicang (stock_code, stock_name, buy_time, buy_price, sell_time, sell_price, buy_quantity, profit_rate, profit_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)', // 新增: sell_price
      [stock_code, stock_name, formattedBuyTime, buy_price, formattedSellTime, sell_price, buy_quantity, profit_rate, profit_amount] // 新增: sell_price
    );
    res.status(201).json({ message: '持仓记录创建成功', id: result.insertId });
  } catch (error) {
    console.error('Error creating chicang record:', error);
    if (error.code === 'ER_NO_SUCH_TABLE') {
        return res.status(500).json({ error: 'Table deal_data.chicang does not exist. It should be created on server start.' });
    } else if (error.code && error.code.startsWith('ER_')) { // 更通用的MySQL错误处理
        return res.status(500).json({ error: 'Database error while creating record.', details: error.message, code: error.code });
    }
    res.status(500).json({ error: 'Failed to create chicang record', details: error.message });
  }
});

// API: 查询所有持仓记录 (后续可以添加搜索和分页)
app.get('/api/chicang', async (req, res) => {
  try {
    // 简单查询所有，后续可以根据 req.query 添加搜索条件
    // 例如: /api/chicang?stock_code=000001
    let query = 'SELECT * FROM deal_data.chicang ORDER BY created_at DESC';
    const queryParams = [];

    if (req.query.stock_code) {
      query = 'SELECT * FROM deal_data.chicang WHERE stock_code LIKE ? ORDER BY created_at DESC';
      queryParams.push(`%${req.query.stock_code}%`);
    }
    // 可以添加更多搜索条件，例如 stock_name

    const [rows] = await db.pool.query(query, queryParams);
    res.status(200).json(rows);
  } catch (error) {
    console.error('Error fetching chicang records:', error);
     if (error.code === 'ER_NO_SUCH_TABLE') {
        return res.status(500).json({ error: 'Table deal_data.chicang does not exist. It should be created on server start.' });
    } else if (error.code && error.code.startsWith('ER_')) {
        return res.status(500).json({ error: 'Database error while fetching records.', details: error.message, code: error.code });
    }
    res.status(500).json({ error: 'Failed to fetch chicang records', details: error.message });
  }
});

// API: 更新一条持仓记录
app.put('/api/chicang/:id', async (req, res) => {
  try {
    const { id } = req.params;
    let {
      stock_code,
      stock_name,
      buy_time,
      buy_price,
      sell_time = null,
      sell_price = null, // 新增：接收 sell_price，默认为 null
      buy_quantity,
      profit_rate = null,
      profit_amount = null
    } = req.body;

    // 基本验证
    if (!stock_code || !buy_time || buy_price === undefined || buy_quantity === undefined) {
      return res.status(400).json({ error: 'Missing required fields: stock_code, buy_time, buy_price, buy_quantity' });
    }

    const formatDateTimeForMySQL = (isoString) => {
      if (!isoString) return null;
      try {
        return new Date(isoString).toISOString().slice(0, 19).replace('T', ' ');
      } catch (e) {
        console.error('Error formatting date:', isoString, e);
        return null;
      }
    };

    const formattedBuyTime = formatDateTimeForMySQL(buy_time);
    const formattedSellTime = formatDateTimeForMySQL(sell_time);

    if (buy_time && !formattedBuyTime) {
        return res.status(400).json({ error: 'Invalid buy_time format. Please use a valid ISO date string.' });
    }
    if (sell_time && !formattedSellTime) {
        return res.status(400).json({ error: 'Invalid sell_time format. Please use a valid ISO date string.' });
    }

    const [result] = await db.pool.query(
      'UPDATE deal_data.chicang SET stock_code = ?, stock_name = ?, buy_time = ?, buy_price = ?, sell_time = ?, sell_price = ?, buy_quantity = ?, profit_rate = ?, profit_amount = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?', // 新增: sell_price = ?
      [stock_code, stock_name, formattedBuyTime, buy_price, formattedSellTime, sell_price, buy_quantity, profit_rate, profit_amount, id] // 新增: sell_price
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'Record not found or no changes made' });
    }
    res.status(200).json({ message: '持仓记录更新成功', id: id });
  } catch (error) {
    console.error('Error updating chicang record:', error);
    if (error.code && error.code.startsWith('ER_')) {
        return res.status(500).json({ error: 'Database error while updating record.', details: error.message, code: error.code });
    }
    res.status(500).json({ error: 'Failed to update chicang record', details: error.message });
  }
});

// API: 删除一条持仓记录
app.delete('/api/chicang/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [result] = await db.pool.query('DELETE FROM deal_data.chicang WHERE id = ?', [id]);

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'Record not found' });
    }
    res.status(200).json({ message: '持仓记录删除成功' });
  } catch (error) {
    console.error('Error deleting chicang record:', error);
    if (error.code && error.code.startsWith('ER_')) {
        return res.status(500).json({ error: 'Database error while deleting record.', details: error.message, code: error.code });
    }
    res.status(500).json({ error: 'Failed to delete chicang record', details: error.message });
  }
});

// API: 查询最新的random_picks表数据
app.get('/api/latest_random_picks', async (req, res) => {
  try {
    // 首先查询所有符合pattern的表名
    const [tables] = await db.pool.query(`
      SELECT TABLE_NAME 
      FROM information_schema.TABLES 
      WHERE TABLE_SCHEMA = 'result' 
      AND TABLE_NAME LIKE 'random_picks_%'
      ORDER BY TABLE_NAME DESC 
      LIMIT 1
    `);
    console.log('Found random_picks tables:', tables);

    if (!tables || tables.length === 0) {
      return res.status(404).json({ error: 'No random_picks tables found' });
    }

    const latestTable = tables[0].TABLE_NAME;
    // 查询最新表中的数据
    const [rows] = await db.pool.query(`SELECT * FROM result.${latestTable}`);
    res.status(200).json({
      table_name: latestTable,
      data: rows
    });

  } catch (error) {
    console.error('Error fetching latest random picks data:', error);
    res.status(500).json({ 
      error: 'Failed to retrieve data from latest random_picks table', 
      details: error.message 
    });
  }
});

// 启动服务器并测试数据库连接
async function startServer() {
  const dbConnected = await db.testConnection();
  if (dbConnected) {
    // 数据库连接成功后，尝试创建 chicang 表 (如果不存在)
    try {
      await db.pool.query(createChicangTableSQL);
      console.log('Table deal_data.chicang checked/created successfully.');
    } catch (tableError) {
      console.error('Error creating/checking deal_data.chicang table:', tableError);
      // 根据情况决定是否要因此退出服务
      // process.exit(1);
    }

    app.listen(PORT, () => {
      console.log(`Server is running on port ${PORT}.`);
      console.log(`Access it at http://localhost:${PORT}`);
    });
  } else {
    console.error('Failed to start server due to database connection issues. Please check your .env configuration and MySQL server.');
    process.exit(1); // 数据库连接失败则退出
  }
}

startServer();